﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Windows.Forms;
using System.Collections;



namespace GEA
{
    class clDatabase
    {
        string ConnectionString = "";

        public clDatabase()
        {
            ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["GEA.Properties.Settings.geaConnectionString"].ToString();
        }

        public int GetID(string strQuery)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            
            SqlCommand cmdMaster = new SqlCommand(strQuery, conn);
            if (strQuery != "")
            {
                SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
                cmdCheck.CommandTimeout = 120;
                try
                {
                    object userNameObj = cmdCheck.ExecuteScalar();
                    if (userNameObj != null)
                    {
                        int Res = Convert.ToInt32(userNameObj);
                        return Res;
                    }
                    return -1;
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.ToString());
                    return -1;
                }
            }
            else
            {
                return -1;
            }
        }

        public Utilizador Get_Utilizador(string utilizador, string senha)
        {

            Utilizador objRet = new Utilizador();

            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            var strQuery = "select * from tbUtilizadores where Utilizador='" + utilizador + "' and Senha='" + senha + "'";
            SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
            cmdCheck.CommandTimeout = 120;
            try
            {
                SqlDataReader dataR = cmdCheck.ExecuteReader();
                if (dataR.HasRows)
                {
                    SmartDataReaderSQL dr = new SmartDataReaderSQL(dataR);

                    while (dr.Read())
                    {
                        Utilizador Ut = new Utilizador();
                        Ut.IdUtilizador = dr.GetInt32(0);
                        Ut.Name_User = dr.GetString(1);
                        Ut.IdNivel = dr.GetInt32(4);
                        Ut.Activo = dr.GetBoolean(3);

                        objRet = Ut;
                    }
                }
                return objRet;
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.ToString());
                return objRet;
            }

        }


        //Entreposto
        public ArrayList Entrepostos_Get_Lista()
        {

            ArrayList objRet = new ArrayList();

            SqlConnection conn = new SqlConnection(ConnectionString);
            
            conn.Open();
            string strQuery = "SELECT * FROM Entreposto ";
            if (int.Parse(GlobalVar.GlobalValue.ToString()) != 0)
                strQuery += " WHERE activo=1 ";
            strQuery += " order by data_alteracao desc";
            SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
            cmdCheck.CommandTimeout = 120;
            try
            {
                SqlDataReader dataR = cmdCheck.ExecuteReader();
                if (dataR.HasRows)
                {
                    SmartDataReaderSQL dr = new SmartDataReaderSQL(dataR);

                    while (dr.Read())
                    {
                        Entreposto Ent = new Entreposto();
                        Ent.Id_user = dr.GetInt32("id_user");
                        Ent.Data_criacao = dr.GetDateTime("data_criacao");
                        Ent.Data_alteracao = dr.GetDateTime("data_alteracao");
                        Ent.Id_entreposto = dr.GetString("id_entreposto");
                        Ent.Tipo = dr.GetString("tipo");
                        Ent.Nome_firma = dr.GetString("nome_firma");
                        Ent.Activo = dr.GetBoolean("activo");
                        Ent.Guid = Guid.Parse(dr.GetString("guid"));

                        objRet.Add(Ent);
                    }
                }
                return objRet;
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.ToString());
                return objRet;
            }
            
        }

        public DataTable Entrepostos_Get_Lista_dt()
        {

            DataTable DataTableObject = new DataTable();

            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = conn.CreateCommand();
            
            string strQuery = "SELECT * FROM Entreposto ";
            if (int.Parse(GlobalVar.GlobalValue.ToString()) != 0)
                strQuery += " WHERE activo=1 ";
            strQuery += " order by data_alteracao desc";

            cmd.CommandText = strQuery;
            cmd.CommandTimeout = 120;
            da.SelectCommand = cmd;
            try
            {
                conn.Open();
                DataTableObject.Clear();
                da.Fill(DataTableObject);
                conn.Close();
            }
            catch
            {
                DataTableObject = null;

            }
            return DataTableObject;
        }

        public void Entreposto_Save(Entreposto Ent, string accao)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandTimeout = 120;
                //cmd.CommandText = "";
                if (accao == "novo")
                {
                    cmd.CommandText = "INSERT INTO Entreposto (id_user,data_criacao,data_alteracao,id_entreposto,tipo,nome_firma,activo,guid) VALUES (@user,@alteracao,@alteracao,@id,@t,@nome,1,'"+Guid.NewGuid()+"');";
                }
                else
                {
                    if (accao == "editar")
                    {
                        cmd.CommandText = "UPDATE Entreposto SET activo=0,data_alteracao=@alteracao,id_user=@user WHERE guid=@guid; " +
                        "INSERT INTO Entreposto (id_user,data_criacao,data_alteracao,id_entreposto,tipo,nome_firma,activo,guid) VALUES (@user,@alteracao,@alteracao,@id,@t,@nome,1,'" + Guid.NewGuid() + "');";
                    }
                    else
                    {
                        if (accao == "remover")
                        {
                            //cmd.CommandText = "UPDATE Entreposto SET activo=0,data_alteracao=@alteracao,id_user=@user WHERE id_entreposto=@id and data_criacao=@criacao;";
                            cmd.CommandText = "UPDATE Entreposto SET activo=0,data_alteracao=@alteracao,id_user=@user WHERE guid=@guid;";
                        }
                    }
                }
                
                cmd.Parameters.AddWithValue("@user", Ent.Id_user);
                cmd.Parameters.AddWithValue("@criacao", Ent.Data_criacao);
                cmd.Parameters.AddWithValue("@alteracao", DateTime.Now);
                cmd.Parameters.AddWithValue("@id", Ent.Id_entreposto.Trim());
                cmd.Parameters.AddWithValue("@t", Ent.Tipo);
                cmd.Parameters.AddWithValue("@nome", Ent.Nome_firma);
                cmd.Parameters.AddWithValue("@guid", Ent.Guid);
                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;

                connection.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        public Boolean Entreposto_Existe(String id_entreposto)
        {
            string strQuery = "Select * from Entreposto where id_entreposto='" + id_entreposto + "' and activo = 1;";
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand cmdMaster = new SqlCommand(strQuery, conn);
            cmdMaster.CommandTimeout = 120;
            if (strQuery != "")
            {
                SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
                cmdCheck.CommandTimeout = 120;
                try
                {
                    object userNameObj = cmdCheck.ExecuteScalar();
                    if (userNameObj != null)
                    {
                        
                        return true;
                    }
                    return false;
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.ToString());
                    return false;
                }
            }
            else
            {
                return false;
            }
        
        }

               
        //Processo

        public Boolean Processos_Existe(String id_entreposto)
        {
            string strQuery = "Select * from Processos where id_entreposto='" + id_entreposto + "' and activo = 1;";
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand cmdMaster = new SqlCommand(strQuery, conn);
            cmdMaster.CommandTimeout = 120;
            if (strQuery != "")
            {
                SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
                cmdCheck.CommandTimeout = 120;
                try
                {
                    object userNameObj = cmdCheck.ExecuteScalar();
                    if (userNameObj != null)
                    {

                        return true;
                    }
                    return false;
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.ToString());
                    return false;
                }
            }
            else
            {
                return false;
            }

        }

        public Boolean Processo_Existe(String id_entreposto, string id_processo)
        {
            string strQuery = "Select * from Processos where id_entreposto='" + id_entreposto + "' and id_processo='"+id_processo+"' and activo = 1;";
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand cmdMaster = new SqlCommand(strQuery, conn);
            cmdMaster.CommandTimeout = 120;
            if (strQuery != "")
            {
                SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
                cmdCheck.CommandTimeout = 120;
                try
                {
                    object userNameObj = cmdCheck.ExecuteScalar();
                    if (userNameObj != null)
                    {

                        return true;
                    }
                    return false;
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.ToString());
                    return false;
                }
            }
            else
            {
                return false;
            }

        }

        public void Processo_Save(Processo Proc,string accao)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandTimeout = 120;
                DateTime data = DateTime.Now;

                if (accao == "novo")
                {
                    cmd.CommandText = "INSERT INTO Processos (id_user,data_criacao,data_alteracao,id_entreposto,id_processo,data_processo,id_factura,fornecedor,contra_marca,data_contra_marca,activo,guid) " +
                " VALUES (@user,@criacao,@alteracao,@id_e,@id_p,@data_processo,@id_f,@fornecedor,@c_m,@data_c_m,1,'"+Guid.NewGuid()+"');";
                }
                else
                {
                    if (accao == "editar")
                    {
                        cmd.CommandText = "UPDATE Processos SET activo=0,data_alteracao=@alteracao,id_user=@user WHERE  guid=@guid; " +
                        "INSERT INTO Processos (id_user,data_criacao,data_alteracao,id_entreposto,id_processo,data_processo,id_factura,fornecedor,contra_marca,data_contra_marca,activo,guid) " +
                        " VALUES (@user,@alteracao,@alteracao,@id_e,@id_p,@data_processo,@id_f,@fornecedor,@c_m,@data_c_m,1,'" + Guid.NewGuid() + "');";
                    }
                    else
                    {
                        if (accao == "remover")
                        {
                            cmd.CommandText = "UPDATE Processos SET activo=0,data_alteracao=@alteracao,id_user=@user WHERE guid=@guid; ";
                        }
                    }
                }



                cmd.Parameters.AddWithValue("@user", Proc.Id_user);
                cmd.Parameters.AddWithValue("@criacao", Proc.Data_criacao.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.Parameters.AddWithValue("@alteracao", DateTime.Now);
                cmd.Parameters.AddWithValue("@id_e", Proc.Id_entreposto);
                cmd.Parameters.AddWithValue("@id_p", Proc.Id_processo);
                cmd.Parameters.AddWithValue("@data_processo", Proc.Data_processo.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.Parameters.AddWithValue("@id_f", Proc.Id_factura);
                cmd.Parameters.AddWithValue("@fornecedor", Proc.Fornecedor);
                cmd.Parameters.AddWithValue("@c_m", Proc.Contra_marca);
                cmd.Parameters.AddWithValue("@data_c_m", Proc.Data_Contra_marca.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.Parameters.AddWithValue("@guid", Proc.Guid);

                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;

                connection.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        public ArrayList Processos_Get_Lista(string id_entreposto, string id_processo)
        {

            ArrayList objRet = new ArrayList();

            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            var strQuery = "SELECT * FROM Processos T1 WHERE id_entreposto='" + id_entreposto + "' and id_processo='" + id_processo + "' and data_alteracao = (SELECT max(data_alteracao) FROM Entreposto T2 WHERE T1.id_entreposto=T2.id_entreposto and T1.id_processo=T2.id_processo) ";
            SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
            cmdCheck.CommandTimeout = 120;
            try
            {
                SqlDataReader dataR = cmdCheck.ExecuteReader();
                if (dataR.HasRows)
                {
                    SmartDataReaderSQL dr = new SmartDataReaderSQL(dataR);

                    while (dr.Read())
                    {
                        Entreposto Ent = new Entreposto();
                        Ent.Id_user = dr.GetInt32(0);
                        Ent.Data_criacao = dr.GetDateTime(1);
                        Ent.Data_criacao = dr.GetDateTime(2);
                        Ent.Id_entreposto = dr.GetString(3);
                        Ent.Tipo = dr.GetString(4);
                        Ent.Nome_firma = dr.GetString(5);

                        objRet.Add(Ent);
                    }
                }
                return objRet;
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.ToString());
                return objRet;
            }

        }

       

        public Processo Processo_Get(string id_entreposto, string id_processo)
        {

            Processo objRet = new Processo();

            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            var strQuery = "SELECT * FROM Processos T1 WHERE id_entreposto='" + id_entreposto + "' and id_processo='" + id_processo + "' and data_alteracao = (SELECT max(data_alteracao) FROM Processos T2 WHERE T1.id_entreposto=T2.id_entreposto and T1.id_processo=T2.id_processo) ";
            SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
            cmdCheck.CommandTimeout = 120;
            try
            {
                SqlDataReader dataR = cmdCheck.ExecuteReader();
                if (dataR.HasRows)
                {
                    SmartDataReaderSQL dr = new SmartDataReaderSQL(dataR);

                    while (dr.Read())
                    {
                        //pro Ent = new Entreposto();
                        objRet.Id_user = dr.GetInt32(0);
                        objRet.Data_criacao = dr.GetDateTime(1);
                        objRet.Data_criacao = dr.GetDateTime(2);
                        objRet.Id_entreposto = dr.GetString(3);
                        objRet.Id_processo = dr.GetString(4);
                        objRet.Data_processo = dr.GetDateTime(5);
                        objRet.Fornecedor = dr.GetString(6);
                        objRet.Id_factura = dr.GetString(7);
                        objRet.Contra_marca = dr.GetString(8);
                        objRet.Data_Contra_marca = dr.GetDateTime(9);

                        //objRet.Add(Ent);
                    }
                }
                return objRet;
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.ToString());
                return objRet;
            }

        }

        public DataTable Processos_Get_Lista_dt(string id_entreposto)
        {

            DataTable DataTableObject = new DataTable();

            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 120;
            string strQuery = "SELECT * FROM Processos T1 WHERE id_entreposto='"+id_entreposto+"' ";

            if (int.Parse(GlobalVar.GlobalValue.ToString()) != 0)
                strQuery += " and activo=1 ";
            //strQuery += " order by id_processo desc";
            strQuery += " order by data_alteracao desc";

            cmd.CommandText = strQuery;
            da.SelectCommand = cmd;
            try
            {
                conn.Open();
                DataTableObject.Clear();
                da.Fill(DataTableObject);
                conn.Close();
            }
            catch
            {
                DataTableObject = null;

            }
            return DataTableObject;
        }

        public DataTable Processos_Get_Proc_Lista_dt(string id_entreposto,string id_processo)
        {

            DataTable DataTableObject = new DataTable();

            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 120;
            string strQuery = "SELECT * FROM Processos T1 WHERE id_entreposto='" + id_entreposto + "' and id_processo like '%" + id_processo + "%'";
            if (int.Parse(GlobalVar.GlobalValue.ToString()) != 0)
                strQuery += " and activo=1";
            strQuery += " order by data_alteracao desc";

            cmd.CommandText = strQuery;
            da.SelectCommand = cmd;
            try
            {
                conn.Open();
                DataTableObject.Clear();
                da.Fill(DataTableObject);
                conn.Close();
            }
            catch
            {
                DataTableObject = null;

            }
            return DataTableObject;
        }


        //DU's

        public Boolean DUs_Existe(String id_entreposto, string id_processo)
        {
            string strQuery = "Select * from DUs where id_entreposto='" + id_entreposto + "' and id_processo='" + id_processo + "' and activo = 1;";
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand cmdMaster = new SqlCommand(strQuery, conn);
            cmdMaster.CommandTimeout = 120;
            if (strQuery != "")
            {
                SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
                cmdCheck.CommandTimeout = 120;
                try
                {
                    object userNameObj = cmdCheck.ExecuteScalar();
                    if (userNameObj != null)
                    {

                        return true;
                    }
                    return false;
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.ToString());
                    return false;
                }
            }
            else
            {
                return false;
            }

        }

        public Boolean DU_Existe(String id_entreposto, string id_processo,string id_du)
        {
            string strQuery = "Select * from DUs where id_entreposto='" + id_entreposto + "' and id_processo='" + id_processo + "' and id_du='"+id_du+"' and activo = 1;";
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand cmdMaster = new SqlCommand(strQuery, conn);
            cmdMaster.CommandTimeout = 120;
            if (strQuery != "")
            {
                SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
                cmdCheck.CommandTimeout = 120;
                try
                {
                    object userNameObj = cmdCheck.ExecuteScalar();
                    if (userNameObj != null)
                    {

                        return true;
                    }
                    return false;
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.ToString());
                    return false;
                }
            }
            else
            {
                return false;
            }

        }
        public Boolean DU_Get_Tipo(string id_e, string id_p)
        {

            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            var strQuery = "SELECT du_entrada  FROM DUs as T1 WHERE id_entreposto='" + id_e + "' and id_processo='" + id_p + "' and activo=1 ";
            SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
            cmdCheck.CommandTimeout = 120;
            try
            {
                SqlDataReader dataR = cmdCheck.ExecuteReader();
                if (dataR.HasRows)
                {
                    SmartDataReaderSQL dr = new SmartDataReaderSQL(dataR);

                    while (dr.Read())
                    {
                        //Entreposto Ent = new Entreposto();
                        return true;
                    }
                }
                else
                    return false;
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.ToString());
                return false;
            }
            return false;
        }

        public Boolean DU_Get_Id(string id_e, string id_p,string id_d)
        {

            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            var strQuery = "SELECT du_entrada  FROM DUs as T1 " +
                "WHERE id_entreposto='" + id_e + "' and id_processo='" + id_p + "' and id_du='" + id_d + "' and activo='1' ";
            SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
            cmdCheck.CommandTimeout = 120;
            try
            {
                SqlDataReader dataR = cmdCheck.ExecuteReader();
                if (dataR.HasRows)
                {
                    SmartDataReaderSQL dr = new SmartDataReaderSQL(dataR);

                    while (dr.Read())
                    {
                        //Entreposto Ent = new Entreposto();
                        return true;
                    }
                }
                else
                    return false;
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.ToString());
                return false;
            }
            return false;
        }

        
        public void DU_Save(DU Du,string accao)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandTimeout = 120;
                DateTime data = DateTime.Now;


                if (accao == "novo")
                {
                    cmd.CommandText = "INSERT INTO DUs (id_user,data_criacao,data_alteracao,id_entreposto,id_processo,id_du,data_du,alfandega,regime,du_entrada,activo,guid) " +
                " VALUES (@user,@criacao,@alteracao,@id_e,@id_p,@id_d,@data_du,@alfandega,@regime,@du_entrada,1,'" + Guid.NewGuid() + "');";
                }
                else
                {
                    if (accao == "editar")
                    {
                        cmd.CommandText = "UPDATE DUs SET activo=0,data_alteracao=@alteracao,id_user=@user WHERE guid=@guid; " +
                        "INSERT INTO DUs (id_user,data_criacao,data_alteracao,id_entreposto,id_processo,id_du,data_du,alfandega,regime,du_entrada,activo,guid) " +
                        " VALUES (@user,@alteracao,@alteracao,@id_e,@id_p,@id_d,@data_du,@alfandega,@regime,@du_entrada,1,'" + Guid.NewGuid() + "');";
                    }
                    else
                    {
                        if (accao == "remover")
                        {
                            cmd.CommandText = "UPDATE DUs SET activo=0,data_alteracao=@alteracao,id_user=@user WHERE guid=@guid; ";
                        }
                    }
                }
               
                cmd.Parameters.AddWithValue("@user", Du.Id_user);
                cmd.Parameters.AddWithValue("@criacao", Du.Data_criacao.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.Parameters.AddWithValue("@alteracao", DateTime.Now);
                cmd.Parameters.AddWithValue("@id_e", Du.Id_entreposto);
                cmd.Parameters.AddWithValue("@id_p", Du.Id_processo);
                cmd.Parameters.AddWithValue("@id_d", Du.Id_DU);
                cmd.Parameters.AddWithValue("@data_du", Du.Data_DU.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.Parameters.AddWithValue("@alfandega", Du.Alfandega);
                cmd.Parameters.AddWithValue("@regime", Du.Regime);
                cmd.Parameters.AddWithValue("@du_entrada", Du.DU_Entrada);
                cmd.Parameters.AddWithValue("@guid", Du.Guid);

                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;

                connection.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        public ArrayList DU_Get_Lista(string id_entreposto, string id_processo)
        {

            ArrayList objRet = new ArrayList();

            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            var strQuery = "SELECT id_du, Activo, Alfandega, Data_alteracao, Data_criacao, Data_DU, DU_Entrada FROM DUs T1 WHERE id_entreposto='" + id_entreposto + "'and id_processo='" + id_processo + "' ";
            
            if (int.Parse(GlobalVar.GlobalValue.ToString()) != 0)
                strQuery += " and activo=1";

            SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
            cmdCheck.CommandTimeout = 120;
            try
            {
                SqlDataReader dataR = cmdCheck.ExecuteReader();
                if (dataR.HasRows)
                {
                    SmartDataReaderSQL dr = new SmartDataReaderSQL(dataR);

                    while (dr.Read())
                    {
                        DU Du = new DU();
                        Du.Id_DU = dr.GetString("id_du");
                        Du.Activo = dr.GetBoolean("Activo");
                        Du.Alfandega = dr.GetString("Alfandega");
                        Du.Data_alteracao = dr.GetDateTime("Data_Alteracao");
                        Du.Data_criacao = dr.GetDateTime("Data_criacao");
                        Du.Data_DU = dr.GetDateTime("Data_DU");
                        Du.DU_Entrada = dr.GetBoolean("DU_Entrada");
                        objRet.Add(Du);
                    }
                }
                return objRet;
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.ToString());
                return objRet;
            }

        }

        public DataTable DU_Get_Lista_dt(string id_entreposto,string id_processo)
        {
            DataTable DataTableObject = new DataTable();

            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 120;
            string strQuery = "SELECT * FROM DUs T1 WHERE id_entreposto='" + id_entreposto + "'and id_processo='" + id_processo + "' ";
            if (int.Parse(GlobalVar.GlobalValue.ToString()) != 0)
                strQuery += " and activo=1";
            strQuery += " order by data_alteracao desc";

            cmd.CommandText = strQuery;
            
            da.SelectCommand = cmd;
            try
            {
                conn.Open();
                DataTableObject.Clear();
                da.Fill(DataTableObject);
                conn.Close();
            }
            catch
            {
                DataTableObject = null;

            }
            if(DataTableObject.Rows.Count > 0)
                return DataTableObject;
            else
                return null;

        }

        public Boolean DU_Is_Last_Id(string id_e, string id_p, string id_d)
        {

            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            var strQuery = "SELECT top(1) id_du FROM DUs " +
                            "WHERE id_entreposto='"+id_e+"' and id_processo='"+id_p+"' and  activo=1 order by data_alteracao desc";
            SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
            cmdCheck.CommandTimeout = 120;
            try
            {
                SqlDataReader dataR = cmdCheck.ExecuteReader();
                if (dataR.HasRows)
                {
                    SmartDataReaderSQL dr = new SmartDataReaderSQL(dataR);

                    while (dr.Read())
                    {
                        if (dr.GetString("id_du") == id_d)
                            return true;
                        else
                            return false;
                    }
                }
                else
                    return false;
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.ToString());
                return false;
            }
            return false;
        }


        //Mercadorias
        public void Mercadoria_Save(Mercadoria mercadoria,string accao)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandTimeout = 120;
                DateTime data = DateTime.Now;
                int Qt;
                Decimal V;
                Decimal PB;
                Decimal PL;

                if (DU_Get_Tipo(mercadoria.Id_entreposto, mercadoria.Id_processo))
                {

                    Qt = -mercadoria.Quantidade;
                    V = -mercadoria.Valor;
                    PB = -mercadoria.Peso_Bruto;
                    PL = -mercadoria.Peso_Liquido;
                }
                else
                {
                    Qt = mercadoria.Quantidade;
                    V = mercadoria.Valor;
                    PB = mercadoria.Peso_Bruto;
                    PL = mercadoria.Peso_Liquido;
                }


                if (accao == "novo")
                {
                    cmd.CommandText = "INSERT INTO Mercadorias (id_user,data_criacao,data_alteracao,id_entreposto,id_processo,id_du,id_referencia,quantidade,valor,peso_bruto,peso_liquido,activo,saldo,guid) " +
                " VALUES (@user,@criacao,@alteracao,@id_e,@id_p,@id_d,@id_r,@quantidade,@valor,@peso_b,@peso_l,1,@saldo,'" + Guid.NewGuid() + "');";
                }
                else
                {
                    if (accao == "editar")
                    {
                        cmd.CommandText = "UPDATE Mercadorias SET activo=0,data_alteracao=@alteracao,id_user=@user  WHERE id_entreposto=@id_e and id_processo=@id_p and id_du=@id_d and activo=1; " +
                        "INSERT INTO Mercadorias (id_user,data_criacao,data_alteracao,id_entreposto,id_processo,id_du,id_referencia,quantidade,valor,peso_bruto,peso_liquido,activo,saldo,guid) " +
                        " VALUES (@user,@alteracao,@alteracao,@id_e,@id_p,@id_d,@id_r,@quantidade,@valor,@peso_b,@peso_l,1,@saldo,'" + Guid.NewGuid() + "');";
                    }
                    else
                    {
                        if (accao == "remover")
                        {
                            cmd.CommandText = "UPDATE Mercadorias SET activo=0,data_alteracao=@alteracao,id_user=@user  WHERE id_entreposto=@id_e and id_processo=@id_p and id_du=@id_d and activo=1; ";
                        }
                    }
                }

                cmd.Parameters.AddWithValue("@user", mercadoria.Id_user);
                cmd.Parameters.AddWithValue("@criacao", mercadoria.Data_criacao.ToString("yyyy-MM-dd HH:mm:ss"));
                cmd.Parameters.AddWithValue("@alteracao", DateTime.Now);
                cmd.Parameters.AddWithValue("@id_e", mercadoria.Id_entreposto);
                cmd.Parameters.AddWithValue("@id_p", mercadoria.Id_processo);
                cmd.Parameters.AddWithValue("@id_d", mercadoria.Id_DU);
                cmd.Parameters.AddWithValue("@id_r", mercadoria.Id_Referencia);
                cmd.Parameters.AddWithValue("@quantidade", Qt);
                cmd.Parameters.AddWithValue("@valor", V);
                cmd.Parameters.AddWithValue("@peso_b", PB);
                cmd.Parameters.AddWithValue("@peso_l", PL);
                cmd.Parameters.AddWithValue("@saldo", mercadoria.Saldo);
                cmd.Parameters.AddWithValue("@guid", mercadoria.Guid);

                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;

                connection.Open();
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

        }

        public DataTable Mercadorias_Get_Lista_DU_Entrada(string id_entreposto, string id_processo)
        {
            DataTable DataTableObject = new DataTable();

            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 120;
            cmd.CommandText = "SELECT T1.id_referencia,abs(quantidade),abs(valor),abs(peso_bruto),abs(peso_liquido) "+
                "FROM Mercadorias T1, DUs as D " +
                "WHERE T1.id_entreposto='"+id_entreposto+"'and T1.id_processo='"+id_processo+"' and D.du_entrada=1 and T1.id_entreposto = D.id_entreposto and T1.id_processo = D.id_processo and T1.id_du = D.id_du " +
                "and T1.data_alteracao = "+
                "( " +
	            "    SELECT max(data_alteracao) "+
	            "    FROM Mercadorias T2 "+
	            "    WHERE T1.id_entreposto=T2.id_entreposto and T1.id_processo=T2.id_processo and T1.id_du = T2.id_du "+
                ") ";
            da.SelectCommand = cmd;
            try
            {
                conn.Open();
                DataTableObject.Clear();
                da.Fill(DataTableObject);
                conn.Close();
            }
            catch
            {
                DataTableObject = null;

            }
            return DataTableObject;
        }

        public DataTable Mercadorias_Get_Lista_dt(string id_entreposto, string id_processo,string id_du, Boolean activo, DateTime data_criacao)
        {
            DataTable DataTableObject = new DataTable();
            int act = 0;

            if (activo)
                act = 1;


            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 120;
            cmd.CommandText = "SELECT id_referencia,abs(quantidade) as quantidade,abs(valor) as valor,abs(peso_bruto) as peso_bruto,abs(peso_liquido) as peso_liquido,saldo, id_user,data_criacao,data_alteracao,id_entreposto,id_processo,id_du " +
                " FROM Mercadorias T1 WHERE id_entreposto='" + id_entreposto + "'and id_processo='" + id_processo + "' and id_du='" + id_du + "' and activo=" + act + " and data_criacao='" + data_criacao.ToString("yyyy-MM-dd HH:mm:ss") + "' ";
            cmd.CommandText += " order by id_referencia";
            da.SelectCommand = cmd;
            try
            {
                conn.Open();
                DataTableObject.Clear();
                da.Fill(DataTableObject);
                conn.Close();
            }
            catch
            {
                DataTableObject = null;

            }
            return DataTableObject;
        }

        public DataTable Mercadorias_Get_Lista_dt(string id_entreposto, string id_processo, string id_du, Boolean activo)
        {
            DataTable DataTableObject = new DataTable();
            int act = 0;

            if (activo)
                act = 1;


            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 120;
            cmd.CommandText = "SELECT id_referencia,abs(quantidade) as quantidade,abs(valor) as valor,abs(peso_bruto) as peso_bruto,abs(peso_liquido) as peso_liquido,saldo, id_user,data_criacao,data_alteracao,id_entreposto,id_processo,id_du " +
                " FROM Mercadorias T1 WHERE id_entreposto='" + id_entreposto + "'and id_processo='" + id_processo + "' and id_du='" + id_du + "' and activo=1  ";
            cmd.CommandText += " order by id_referencia";
            da.SelectCommand = cmd;
            try
            {
                conn.Open();
                DataTableObject.Clear();
                da.Fill(DataTableObject);
                conn.Close();
            }
            catch
            {
                DataTableObject = null;

            }
            return DataTableObject;
        }


        public DataTable Mercadorias_Get_Lista_dt(string id_entreposto, string id_processo, string id_du)
        {
            DataTable DataTableObject = new DataTable();

            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 120;
            cmd.CommandText = "SELECT id_referencia,abs(quantidade) as quantidade,abs(valor) as valor,abs(peso_bruto) as peso_bruto,abs(peso_liquido) as peso_liquido, id_user,data_criacao,data_alteracao,id_entreposto,id_processo,id_du " +
                " FROM Mercadorias T1 WHERE id_entreposto='" + id_entreposto + "'and id_processo='" + id_processo + "' and id_du='" + id_du + "' and activo=1  order by id_referencia";
            da.SelectCommand = cmd;
            try
            {
                conn.Open();
                DataTableObject.Clear();
                da.Fill(DataTableObject);
                conn.Close();
            }
            catch
            {
                DataTableObject = null;

            }
            return DataTableObject;
        }

        public DataTable Mercadorias_Get_Lista_Ref_dt(string id_entreposto, string referencia)
        {
            DataTable DataTableObject = new DataTable();

            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 120;
            cmd.CommandText = "SELECT distinct(id_referencia),sum(quantidade) as stock,sum(valor) as valor,sum(peso_bruto) as peso_bruto,sum(peso_liquido) as peso_liquido " +
                "FROM Mercadorias T1 WHERE id_entreposto='" + id_entreposto + "' and T1.id_referencia like'%" + referencia + "%' and activo=1 group by id_referencia order by id_referencia";
            da.SelectCommand = cmd;
            try
            {
                conn.Open();
                DataTableObject.Clear();
                da.Fill(DataTableObject);
                conn.Close();
            }
            catch
            {
                DataTableObject = null;

            }
            return DataTableObject;
        }

        public DataTable Mercadorias_Get_Lista_Stock_dt(string id_entreposto, string id_processo)
        {
            DataTable DataTableObject = new DataTable();

            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 120;
            cmd.CommandText = "select id_referencia,sum(quantidade) as Saldo, 0 as 'Qtd. a Movimentar', sum(valor) as Valor, sum(peso_bruto) as Peso_Bruto, sum(peso_liquido) as Peso_Liquido " +
                "from Mercadorias where id_entreposto='" + id_entreposto + "' and  id_processo = '" + id_processo + "' and activo=1 group by   id_referencia order by id_referencia";
            da.SelectCommand = cmd;
            try
            {
                conn.Open();
                DataTableObject.Clear();
                da.Fill(DataTableObject);
                conn.Close();
            }
            catch
            {
                DataTableObject = null;

            }
            return DataTableObject;
        }

        public DataTable Mercadorias_Get_Lista_Stock_dt(string id_entreposto, string id_processo,string id_du)
        {
            DataTable DataTableObject = new DataTable();

            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 120;
            cmd.CommandText = "select id_referencia ,sum( abs(quantidade) + saldo) as Saldo, abs(quantidade) as quantidade, sum(valor) as valor, sum(peso_bruto) as peso_bruto, sum(peso_liquido) as peso_liquido, '"+id_du+"' as id_du "  +
                "from Mercadorias where id_entreposto='" + id_entreposto + "' and  id_processo = '" + id_processo + "' and id_du = '" + id_du + "' and activo=1 group by   id_referencia,quantidade  order by id_referencia";
            da.SelectCommand = cmd;
            try
            {
                conn.Open();
                DataTableObject.Clear();
                da.Fill(DataTableObject);
                conn.Close();
            }
            catch
            {
                DataTableObject = null;

            }
            return DataTableObject;
        }




        public ArrayList DU_Get_DU_Entrada(string id_entreposto, string id_processo)
        {

            ArrayList objRet = new ArrayList();

            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            var strQuery = "select id_referencia,sum(quantidade) as Saldo,  sum(valor) as Valor, sum(peso_bruto) as Peso_Bruto, sum(peso_liquido) as Peso_Liquido " +
                " from Mercadorias as M,DUs as D " +
                " WHERE M.id_entreposto = D.id_entreposto and M.id_processo = D.id_processo and D.du_entrada=1 and m.id_du = D.id_du" +
                " and M.id_entreposto='" + id_entreposto + "' and M.id_processo='" + id_processo + "' and D.activo=1 group by   id_referencia ";


            //if (int.Parse(GlobalVar.GlobalValue.ToString()) != 0)
            //    strQuery += " and activo=1";

            SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
            cmdCheck.CommandTimeout = 120;
            try
            {
                SqlDataReader dataR = cmdCheck.ExecuteReader();
                if (dataR.HasRows)
                {
                    SmartDataReaderSQL dr = new SmartDataReaderSQL(dataR);

                    while (dr.Read())
                    {
                        Mercadoria Merc = new Mercadoria();
                        Merc.Id_Referencia = dr.GetString("id_referencia");
                        Merc.Valor = Decimal.Parse(dr.GetString("valor"));
                        Merc.Peso_Bruto = Decimal.Parse(dr.GetString("peso_bruto"));
                        Merc.Peso_Liquido = Decimal.Parse(dr.GetString("peso_liquido"));
                        Merc.Quantidade = dr.GetInt32("saldo");

                        objRet.Add(Merc);
                    }
                }
                return objRet;
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.ToString());
                return objRet;
            }

        }

        public Mercadoria DU_Get_DU_Entrada(string id_entreposto, string id_processo,string id_referencia)
        {

            Mercadoria objRet = new Mercadoria();

            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            var strQuery = "select id_referencia,sum(quantidade) as Saldo,  sum(valor) as Valor, sum(peso_bruto) as Peso_Bruto, sum(peso_liquido) as Peso_Liquido " +
                " from Mercadorias as M,DUs as D " +
                " WHERE M.id_entreposto = D.id_entreposto and M.id_processo = D.id_processo and D.du_entrada=1 and m.id_du = D.id_du" +
                " and M.id_entreposto='" + id_entreposto + "' and M.id_processo='" + id_processo + "' and M.id_referencia='"+id_referencia+"' and D.activo=1 group by   id_referencia ";


            //if (int.Parse(GlobalVar.GlobalValue.ToString()) != 0)
            //    strQuery += " and activo=1";

            SqlCommand cmdCheck = new SqlCommand(strQuery, conn);
            cmdCheck.CommandTimeout = 120;
            try
            {
                SqlDataReader dataR = cmdCheck.ExecuteReader();
                if (dataR.HasRows)
                {
                    SmartDataReaderSQL dr = new SmartDataReaderSQL(dataR);

                    while (dr.Read())
                    {
                        Mercadoria Merc = new Mercadoria();
                        Merc.Id_Referencia = dr.GetString("id_referencia");
                        Merc.Valor = Decimal.Parse(dr.GetString("valor"));
                        Merc.Peso_Bruto = Decimal.Parse(dr.GetString("peso_bruto"));
                        Merc.Peso_Liquido = Decimal.Parse(dr.GetString("peso_liquido"));
                        Merc.Quantidade = dr.GetInt32("saldo");

                        objRet = Merc;
                    }
                }
                return objRet;
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.ToString());
                return objRet;
            }

        }


        //Reports
        public DataTable Get_Report(string id_ent,string like)
        {


            DataTable DataTableObject = new DataTable();

            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter da = new SqlDataAdapter();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandTimeout = 120;
            cmd.CommandText = "select M.id_processo,M.id_du,M.id_referencia from Mercadorias as M " +
                " where M.id_entreposto  = '" + id_ent + "' and M.activo=1 " +
                " and (M.id_processo like '%"+ like +"%' or M.id_du like '%"+ like +"%' or M.id_referencia like '%"+ like +"%' )" +
                " order by data_alteracao,id_processo,M.id_du,id_referencia ";
            da.SelectCommand = cmd;
            try
            {
                conn.Open();
                DataTableObject.Clear();
                da.Fill(DataTableObject);
                conn.Close();
            }
            catch
            {
                DataTableObject = null;

            }
            return DataTableObject;

        }


    }

  



   
}
